package dao.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.FacilityDTO;
import model.FacilitySearchbyTypeDTO;
import dao.DAOException;
import dao.FacilityDAO;
import model.AvailableDTO;

public class FacilityDAOImpl implements FacilityDAO {
	private static final String dbUrl = "jdbc:mysql://localhost:3306/facilitybookingsystem";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";

	public void insertFacility(FacilityDTO facility) throws DAOException {
		String insertSql = "INSERT INTO facility (FName,Description,FType,Capacity) VALUES('"
				+ facility.getFacilityName()
				+ "', '"
				+ facility.getDescription()
				+ "', '"
				+ facility.getFacilityType()
				+ "', '"
				+ facility.getCapacity()
				+ "');";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing insert: " + insertSql);
			st.executeUpdate(insertSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public void updateFacility(FacilityDTO facility) throws DAOException {

		String updateSql = "UPDATE facility SET FName = '"
				+ facility.getFacilityName() + "', Description = '"
				+ facility.getDescription() + "', FType = '"
				+ facility.getFacilityType() + "', Capacity= '"
				+ facility.getCapacity() + "' WHERE FacilityID='"
				+ facility.getFacilityID() + "';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + updateSql);
			st.executeUpdate(updateSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public void deleteFacility(FacilityDTO facility) throws DAOException {

		String deleteSql = "DELETE FROM Facility WHERE FacilityID="
				+ facility.getFacilityID() + ";";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing delete: " + deleteSql);
			st.executeUpdate(deleteSql);
			st.close();
		} catch (Exception e) {
			String error = "Error deleting users. Nested Exception is: " + e;
			System.out.println("#$#$#@$#@#@$#@$#@$#$#@");
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public ArrayList<FacilityDTO> findAllFacility() throws DAOException {
		ArrayList<FacilityDTO> facilities = new ArrayList<FacilityDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String selectSql = "SELECT * FROM Facility;";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				FacilityDTO oneFacility = new FacilityDTO();
				oneFacility.setFacilityID(rs.getInt("FacilityID"));
				oneFacility.setFacilityName(rs.getString("FName"));
				oneFacility.setDescription(rs.getString("Description"));
				oneFacility.setFacilityType(rs.getString("FType"));
				oneFacility.setCapacity(rs.getString("Capacity"));
				facilities.add(oneFacility);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return facilities;
	}

	public FacilityDTO findFacility(int facilityID) throws DAOException {
		String selectSql = "SELECT * FROM facility WHERE FacilityID="
				+ facilityID + ";";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		FacilityDTO oneFacility = new FacilityDTO();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				oneFacility.setFacilityID(rs.getInt("FacilityID"));
				oneFacility.setFacilityName(rs.getString("FName"));
				oneFacility.setFacilityType(rs.getString("FType"));
				oneFacility.setDescription(rs.getString("Description"));
				oneFacility.setCapacity(rs.getString("Capacity"));
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		System.out.println(oneFacility.toString());
		return oneFacility;
	}

	public ArrayList<FacilityDTO> searchAvailableFacilityByType(
			FacilitySearchbyTypeDTO fsdto) throws DAOException {
		ArrayList<FacilityDTO> list = new ArrayList<FacilityDTO>();
		// String selectSql = null;
		String selectSql = "select * from facility where ";
		String temp = " and ";
		char c;
		if (fsdto.getFacilityName() != "") {
			selectSql += "FName like '%" + fsdto.getFacilityName() + "%'";
		}
		if (fsdto.getDescription() != "") {
			c=selectSql.charAt(selectSql.length()-1);
			if (c!=' ') {
				selectSql += temp;
			}
			selectSql += "Description like '%" + fsdto.getDescription() + "%'";
		}
		if (fsdto.getFacilityType() != "") {
			c=selectSql.charAt(selectSql.length()-1);
			if (c!=' ') {
				selectSql += temp;
			}
			selectSql += "FType ='" + fsdto.getFacilityType() + "'";
		}
		if (fsdto.getCapacity() != "") {
			c=selectSql.charAt(selectSql.length()-1);
			if (c!=' ') {
				selectSql += temp;
			}
			int i=Integer.valueOf(fsdto.getCapacity());
			selectSql += "Capacity >"+i+"";
		}
		System.out.println(selectSql);
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				FacilityDTO oneFacility = new FacilityDTO();
				// oneFacility.setFacilityName(rs.getString("FacilityID"));
				oneFacility.setFacilityName(rs.getString("FName"));
				oneFacility.setDescription(rs.getString("Description"));
				oneFacility.setFacilityType(rs.getString("FType"));
				oneFacility.setCapacity(rs.getString("Capacity"));
				oneFacility.setFacilityID(rs.getInt("FacilityID"));
				// System.out.println(oneFacility.toString());
				list.add(oneFacility);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return list;
	}

	public ArrayList<FacilityDTO> availableFacility(AvailableDTO booking)
			throws DAOException {
		ArrayList<FacilityDTO> facilities = new ArrayList<FacilityDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		

		String startDateString = df.format(booking.getStartDate());
		String selectSql = "select * from facility where facilityID not in (select facilityID from booking where ((TO_DAYS(StartDate) <= TO_DAYS('"+startDateString
				+"') and TO_DAYS(DATE_ADD(StartDate, INTERVAL period DAY)) >= TO_DAYS('"+startDateString
				+"')) or (TO_DAYS(StartDate) >= TO_DAYS('"+startDateString
				+"') and TO_DAYS(DATE_ADD('"+startDateString
				+"', INTERVAL "+booking.getPeriod()
				+" DAY)) >= TO_DAYS(StartDate)))) and FType ='"+ booking.getFacilityType()
				+"'" ;

		
		
//		String selectSql = "select * from facility where facilityID not in (select facilityID from booking where StartDate between '"
//				+ df.format(booking.getStartDate())
//				+ "' and DATE_ADD('"
//				+ df.format(booking.getStartDate())
//				+ "',"
//				+ " INTERVAL "
//				+ booking.getPeriod()
//				+ " DAY )) and FType ='"
//				+ booking.getFacilityType() + "' ";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				FacilityDTO oneFacility = new FacilityDTO();
				oneFacility.setFacilityID(rs.getInt("FacilityID"));
				oneFacility.setFacilityName(rs.getString("FName"));
				oneFacility.setFacilityType(rs.getString("FType"));
				oneFacility.setDescription(rs.getString("Description"));
				oneFacility.setCapacity(rs.getString("Capacity"));
				facilities.add(oneFacility);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return facilities;
	}
}
